﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StoragePeriod.IDAL.FoodTesting;
using System.Data;
using StoragePeriod.Model.FoodTesting;
using StoragePeriod.DBUtility;
using System.Data.SqlClient;

namespace StoragePeriod.SqlServerDAL.FoodTesting
{
    /// <summary>
    /// 实现原料检测记录 接口
    /// </summary>
    public class D_FoodRecord : I_FoodRecord
    {
        /// <summary>
        /// 根据原料ID和检测日期获取检测记录列表
        /// </summary>
        /// <param name="eFoodRecord"></param>
        /// <returns></returns>
        public DataTable GetFoodRecordListByRawIDS(E_FoodRecord eFoodRecord)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select * from FoodRecord where RawID in (" + eFoodRecord.RawIDS + ")");
            sbSql.Append(" and FoodTypeID=" + eFoodRecord.FoodTypeID + " and FoodTime=CAST('" + eFoodRecord.FoodTime.ToString() + "' as datetime)");
            DataTable dt = DbHelperSQL.Query(sbSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 删除检测记录
        /// </summary>
        /// <param name="eFoodRecord">要删除的检测记录</param>
        /// <returns></returns>
        public int Remove(E_FoodRecord eFoodRecord)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from FoodRecord where id=" + eFoodRecord.id);
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 根据检测时间和原料ID 删除检测记录
        /// </summary>
        /// <param name="eFoodRecord">要删除的检测记录</param>
        /// <returns></returns>
        public int RemoveByFoodTime(E_FoodRecord eFoodRecord)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from FoodRecord where RawID=" + eFoodRecord.RawID);
            strSql.Append(" and FoodTypeID=" + eFoodRecord.FoodTypeID + " and FoodTime=CAST('" + eFoodRecord.FoodTime.ToString() + "' as datetime)");
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 根据原料ID和检测时间以及检测类型获取检测记录
        /// </summary>
        /// <param name="eFoodRecord">要获取的检测记录</param>
        /// <returns></returns>
        public E_FoodRecord GetFoodRecord(E_FoodRecord eFoodRecord)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select * from FoodRecord where RawID="+eFoodRecord.RawID);
            sbSql.Append(" and FoodTypeID=" + eFoodRecord.FoodTypeID + " and FoodTime=CAST('" + eFoodRecord.FoodTime.ToString() + "' as datetime)");
            DataTable dt = DbHelperSQL.Query(sbSql.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                E_FoodRecord Entity = new ModelHandler<E_FoodRecord>().FillModel(dt.Rows[0]);
                return Entity;
            }
            return null;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(E_FoodRecord eFoodReord)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into FoodRecord(");
            strSql.Append("FoodTypeID,RawID,RawName,FoodTime,FoodResult)");
            strSql.Append(" values (");
            strSql.Append("@FoodTypeID,@RawID,@RawName,@FoodTime,@FoodResult)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@FoodTypeID", SqlDbType.Int,4),
					new SqlParameter("@RawID", SqlDbType.Int,4),
					new SqlParameter("@RawName", SqlDbType.NVarChar,50),
					new SqlParameter("@FoodTime", SqlDbType.DateTime),
					new SqlParameter("@FoodResult", SqlDbType.NVarChar,50)};
            parameters[0].Value = eFoodReord.FoodTypeID;
            parameters[1].Value = eFoodReord.RawID;
            parameters[2].Value = eFoodReord.RawName;
            parameters[3].Value = eFoodReord.FoodTime;
            parameters[4].Value = eFoodReord.FoodResult;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(E_FoodRecord eFoodReord)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update FoodRecord set ");
            strSql.Append("FoodTypeID=@FoodTypeID,");
            strSql.Append("RawID=@RawID,");
            strSql.Append("RawName=@RawName,");
            strSql.Append("FoodTime=@FoodTime,");
            strSql.Append("FoodResult=@FoodResult");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@FoodTypeID", SqlDbType.Int,4),
					new SqlParameter("@RawID", SqlDbType.Int,4),
					new SqlParameter("@RawName", SqlDbType.NVarChar,50),
					new SqlParameter("@FoodTime", SqlDbType.DateTime),
					new SqlParameter("@FoodResult", SqlDbType.NVarChar,50),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = eFoodReord.FoodTypeID;
            parameters[1].Value = eFoodReord.RawID;
            parameters[2].Value = eFoodReord.RawName;
            parameters[3].Value = eFoodReord.FoodTime;
            parameters[4].Value = eFoodReord.FoodResult;
            parameters[5].Value = eFoodReord.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取最新的检测结果
        /// </summary>
        /// <param name="eFoodReord"></param>
        /// <returns>返回最新的检测结果</returns>
        public E_FoodRecord GetNewFoodResult(E_FoodRecord eFoodRecord)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select top 1 * from FoodRecord where RawID=" + eFoodRecord.RawID);
            if (eFoodRecord.FoodStartTime != null)
            {
                sbSql.Append(" and FoodTime>=cast('"+eFoodRecord.FoodStartTime.ToString()+"' as datetime)");
            }
            if (eFoodRecord.FoodEndTime != null)
            {
                sbSql.Append(" and FoodTime<=cast('" + eFoodRecord.FoodEndTime.ToString() + "' as datetime)");
            }
            sbSql.Append(" and FoodTypeID=" + eFoodRecord.FoodTypeID + " order by FoodTime desc");
            DataTable dt = DbHelperSQL.Query(sbSql.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                E_FoodRecord Entity = new ModelHandler<E_FoodRecord>().FillModel(dt.Rows[0]);
                return Entity;
            }
            return null;
        }
    }
}
